The TO_NUMBER function is converting the value of BINARY_FLOAT, BINARY_DOUBLE, CHAR,
VARCHAR2, NCHAR, or NVARCHAR2 datatype to a value of NUMBER datatype.
Without providing the format of the input, TO_NUMBER will consider the provided value is compliant with the default format. Relying on
a default configuration is a source of error because it creates a dependency between the code and the configuration of the ORACLE server where this
code is deployed.
The behaviour of the TO_NUMBER function will certainly not be the expected one if the configuration of the ORACLE server is
changing.
Noncompliant code example
The following code with return 0 on an ORACLE server running with its default US configuration with p_string = "2,540"
IF ( TO_NUMBER(p_string) >= 0 and TO_NUMBER(p_string) <= TO_NUMBER('50.00') ) THEN
RETURN 1;
ELSE
RETURN 0;
END IF;
Compliant solution
The following code with return 1 on an ORACLE server running with its default FR configuration with p_string = "2,540" because the comma will be
interpreted as decimal separator instead of thousand separator.
IF ( TO_NUMBER(p_string, '99.99') >= 0 and TO_NUMBER(p_string, '99.99') <= TO_NUMBER('50.00','99.99') ) THEN
RETURN 1;
ELSE
RETURN 0;
END IF;
